use serde::Serialize;
use sqlx::Executor;
use sqlx::Row;
use crate::error::{is_unique_violation, new_error, AppErr, SqlxErr};

use super::get_pool;

const CREATE_SQL: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_vote (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        name TEXT NOT NULL, 
        UNIQUE (name)
    )
"#;

#[derive(Debug, Serialize)]
pub struct Vote {
    pub id: i64,
    pub name: String,
}

pub async fn init() -> Result<(), AppErr> {
    get_pool().execute(CREATE_SQL).await?;

    if is_empty().await? {
        create("第一阶段").await?;
        create("第二阶段").await?;
    }
    Ok(())
}

pub async fn create(name: &str) -> Result<i64, AppErr> {
    let ret = sqlx::query("INSERT INTO tb_vote (name) VALUES (?)")
        .bind(name)
        .execute(get_pool())
        .await
        .map_err(|e| {
            if is_unique_violation(&e) {
                new_error("名称重复")
            } else {
                AppErr::Sqlx(e)
            }
        })?;
    Ok(ret.last_insert_rowid())
}

pub async fn delete(id: i64) -> Result<(), AppErr> {
    sqlx::query("DELETE FROM tb_vote WHERE id = ?")
        .bind(id)
        .execute(get_pool())
        .await?;
    Ok(())
}

pub async fn latest_id() -> Result<i64, AppErr> {
    let row = sqlx::query("SELECT id FROM tb_vote ORDER BY id DESC LIMIT 1")
        .fetch_one(get_pool())
        .await?;
    Ok(row.get(0))
}

pub async fn query() -> Result<Vec<Vote>, AppErr> {
    let rows = sqlx::query("SELECT id, name FROM tb_vote")
        .fetch_all(get_pool())
        .await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let item = Vote {
            id: row.get(0),
            name: row.get(1),
        };
        vec.push(item);
    }
    Ok(vec)
}

async fn is_empty() -> Result<bool, SqlxErr> {
    let rows = sqlx::query("SELECT id FROM tb_vote LIMIT 1").fetch_optional(get_pool()).await?;
    Ok(rows.is_none())
}








